package cup.league.app;

import java.util.Calendar;
import java.util.Date;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DBHandler {

	private static String DB_NAME = "app_cup_league_manager";
	private static int DB_VERSION = 2;
	
	public static String TB_CHAMP = "CHAMPIONSHIP";
	public static String CHA_CL_ID = "CHA_in_id";
	public static String CHA_CL_CHAMPION = "CHA_TEA_in_winner_id";
	public static String CHA_CL_TITLE = "CHA_st_title";
	public static String CHA_CL_NUM_PART = "CHA_in_num_participants";
	public static String CHA_CL_TYPE = "CHA_TYP_in_id";
	public static String CHA_CL_DATE = "CHA_dt_date";
	
	public static String TB_TYPE = "TYPE";
	public static String TYP_CL_ID = "TYP_in_id";
	public static String TYP_CL_TYPE = "TYP_st_type";
	
	public static String TB_TABLE = "C_TABLE";
	public static String TAB_CL_ID = "TAB_in_id";
	public static String TAB_CL_TYPE = "TAB_TYP_in_id";
	public static String TAB_CL_CHAMP = "TAB_CHA_in_id";
	
	public static String TB_TEAM = "TEAM";
	public static String TEA_CL_ID = "TEA_in_id";
	public static String TEA_CL_NAME = "TEA_st_name";
	
	public static String TB_MATCH = "MATCH";
	public static String MAT_CL_ID = "MAT_in_id";
	public static String MAT_CL_CHAMP = "MAT_CHA_in_id";
	public static String MAT_CL_TABLE = "MAT_TAB_in_id";
	public static String MAT_CL_AWAY = "MAT_TEA_in_away_id";
	public static String MAT_CL_HOME = "MAT_TEA_in_home_id";
	public static String MAT_CL_GOALS_AWAY = "MAT_int_goals_away";
	public static String MAT_CL_GOALS_HOME = "MAT_int_goals_home";
	
	private static final String[] DATABASE_CREATE = {
		
		"create table "+TB_TEAM+" ("+
		TEA_CL_ID+" integer PRIMARY KEY autoincrement,"+
		TEA_CL_NAME+" varchar(255) not null"+
		");",
		
		"create table "+TB_TYPE+"("+
		TYP_CL_ID+" integer PRIMARY KEY autoincrement,"+
		TYP_CL_TYPE+" varchar(20) );",	
		
		"create table "+TB_CHAMP+"("+
		CHA_CL_ID+" integer PRIMARY KEY autoincrement,"+
		CHA_CL_TITLE+" varchar(255) not null,"+
		CHA_CL_TYPE+" integer not null REFERENCES "+TB_TYPE+" ("+ TYP_CL_ID +"),"+
		CHA_CL_NUM_PART+" integer not null,"+
		CHA_CL_CHAMPION+" integer REFERENCES "+TB_TEAM+" ("+ TEA_CL_ID +"),"+
		CHA_CL_DATE+" varchar(20)"+
		");",
		
		"create table "+TB_TABLE+"("+
		TAB_CL_ID+" integer PRIMARY KEY autoincrement,"+
		TAB_CL_CHAMP+" integer not null REFERENCES "+TB_CHAMP+"("+ CHA_CL_ID +"),"+
		TAB_CL_TYPE+" integer not null REFERENCES "+TB_TYPE+"("+ TYP_CL_ID +") );",
		
		"create table "+TB_MATCH+"("+
		MAT_CL_ID+" integer PRIMARY KEY autoincrement,"+
		MAT_CL_CHAMP+" integer not null REFERENCES "+TB_CHAMP+"("+ CHA_CL_ID +"),"+
		MAT_CL_TABLE+" integer not null REFERENCES "+TB_TABLE+"("+ TAB_CL_ID +"),"+
		MAT_CL_HOME+" integer not null REFERENCES "+TB_TEAM+"("+ TEA_CL_ID +"),"+
		MAT_CL_AWAY+" integer not null REFERENCES "+TB_TEAM+"("+ TEA_CL_ID +"),"+
		MAT_CL_GOALS_HOME+" integer,"+
		MAT_CL_GOALS_AWAY+" integer );"};
	
	private SQLiteDatabase sdb;
	private DBHelper sdbHelper;
	
	private final Context ctx;
	
	public DBHandler(Context context){
		this.ctx = context;
	}
	
	public DBHandler open(){
		sdbHelper = new DBHelper(ctx);
		sdb = sdbHelper.getWritableDatabase();
		sdb.execSQL("PRAGMA foreign_keys = ON;");
		return this;
		
	}
	
	public void close(){
		sdbHelper.close();
	}
	
	public boolean addChamp(String name, String type, int numpart){
		if(!champExists(name) && typeExists(type)){
			try{
				Cursor cur = getTypeByType(type);
				cur.moveToFirst();
				int typeId = (int) cur.getInt(cur.getColumnIndex(TYP_CL_ID));
				ContentValues values = new ContentValues();
				values.put(CHA_CL_TITLE, name);
				values.put(CHA_CL_NUM_PART, numpart);
				values.put(CHA_CL_TYPE, typeId);
				Date dt = new Date();
				String date = dt.getMonth()+"/"+dt.getDay()+"/"+dt.getYear();
				values.put(CHA_CL_DATE, date);
				sdb.insert(TB_CHAMP, CHA_CL_CHAMPION , values);
				cur.close();
			}
			catch(Exception e){
				Log.e("ERROR","ERROR: "+e.toString());
				e.printStackTrace();
				return false;
			}
			return true;
		}
		return false;
	}
	
	public boolean typeExists(String type){
		if(getTypeByType(type) == null)
			return false;
		else
			return true;
	}
	
	public Cursor getTypeByType(String type){
		Cursor cur = sdb.query(TB_TYPE, null, TYP_CL_TYPE+" = '"+type+"'", null, null, null, null);
		if(cur.getCount() > 0){
			cur.moveToFirst();
			return cur;
		}
		else
			return null;
	}
	
	public Cursor getChampByName(String name){
		Cursor cur = sdb.query(TB_CHAMP, null, CHA_CL_TITLE+" = '"+name+"'", null, null, null, null);
		if (cur != null){
			cur.moveToFirst();
			return cur;
		}
		else
			return null;
	}
	
	public int getIdChampByName(String name){
		Cursor cur = getChampByName(name);
		cur.moveToFirst();
		int c = cur.getInt(cur.getColumnIndex(CHA_CL_ID));
		cur.close();
		return c;
	}
	
	public Cursor getAllChamps(){
		try{
			Cursor cur = sdb.query(TB_CHAMP, null, null, null, null, null, null);
			cur.moveToFirst();
			return cur;
		}catch(Exception e){
			Log.e("ERROR","ERROR: "+e.toString());
			e.printStackTrace();
			return null;
		}
		
	}
	
	public boolean champExists(String name){
		try{
			Cursor cur = sdb.query(TB_CHAMP, new String[] {CHA_CL_TITLE}, CHA_CL_TITLE+" = '"+name+"'", null, null, null, null);
			if(cur.getCount() > 0)
				return true;
			else
				return false;
		}
		catch(Exception e){
			Log.e("ERROR:", e.toString());
			e.printStackTrace();
			return false;
		}
	}
	
	public boolean insertTypes(SQLiteDatabase db){
		try{
			ContentValues type1 = new ContentValues();
			type1.put(TYP_CL_TYPE, "Groups");
			db.insert(TB_TYPE, null, type1);
			
			ContentValues type2 = new ContentValues();
			type2.put(TYP_CL_TYPE, "Playoffs");
			db.insert(TB_TYPE, null, type2);
			
			ContentValues type3 = new ContentValues();
			type3.put(TYP_CL_TYPE, "Both");
			db.insert(TB_TYPE, null, type3);
			
		}
		catch(Exception e){
			Log.e("ERROR", "ERROR: "+e.toString());
			e.printStackTrace();
			return false;
		}
		return true;
	}

	private class DBHelper extends SQLiteOpenHelper {
		
		DBHelper(Context context) {
			super(context, DB_NAME, null, DB_VERSION);
		}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL("PRAGMA foreign_keys = ON;");
		for (int i = 0; i < DATABASE_CREATE.length; i++){
			db.execSQL(DATABASE_CREATE[i]);
		}
		insertTypes(db);
		
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		
	}
	
}
	
}
